INSERT OVERWRITE TABLE Hose_Delivery_res
select  C.Pump_Name,B.Hose_number,D.Grade_Name,from_unixtime(unix_timestamp(A.Completed_TS) - floor(A.Delivery_Volume/33.5*60),'yyyy-MM-dd HH:mm:ss'),floor(A.Delivery_Volume/33.5*60),A.*
from mid.Hose_Delivery as A
 join mid.Hoses as B 
on A.Hose_ID=B.Hose_ID and B.Deleted = 0 and A.station_id = B.station_id 
 join mid.Pumps as C
ON B.Pump_ID = C.Pump_ID and C.Deleted = 0 and C.station_id = B.station_id
 join mid.Grades as D
on D.Grade_id = B.Grade_id and D.Grade_Name not like '%柴油%' and D.Deleted = 0 and D.station_id = B.station_id
union all
select C.Pump_Name,B.Hose_number,D.Grade_Name,from_unixtime(unix_timestamp(A.Completed_TS) - floor(A.Delivery_Volume*1.5),'yyyy-MM-dd HH:mm:ss'),floor(A.Delivery_Volume*1.5),A.*
from mid.Hose_Delivery as A
 join mid.Hoses as B 
on A.Hose_ID=B.Hose_ID and B.Deleted = 0 and A.station_id = B.station_id
 join mid.Pumps as C
ON B.Pump_ID = C.Pump_ID and C.Deleted = 0 and C.station_id = B.station_id
join mid.Grades as D
on D.Grade_id = B.Grade_id and D.Grade_Name  like '%柴油%' and D.Deleted = 0 and D.station_id = B.station_id;


insert OVERWRITE table res_hoseEfficiency
select substr(Completed_TS,1,13),station_id,regexp_extract(pump_name,'[0-9]+',0),hose_number,Grade_Name,sum(work_seconds),sum(Delivery_Volume),sum(Delivery_Value)
from Hose_Delivery_res
group by substr(Completed_TS,1,13),station_id,pump_name,hose_number,Grade_Name;



